//
//  LYSubjectListDBTool.m
//  artist
//
//  Created by Jay_Apple on 16/4/19.
//  Copyright © 2016年 viviyoo. All rights reserved.
//

#import "LYSubjectListDBTool.h"
#import "LYSubDataModel.h"

static NSString *const tableName = @"t_subject";

@implementation LYSubjectListDBTool

+(void)initialize{
    
    
    [VVYSaveDataManager createTableWithName:tableName andKeyTypes:@{
                                                                    @"id" : @"integer NOT NULL UNIQUE",
                                                                    @"pid" : @"integer NOT NULL",
                                                                    @"title" : @"text NOT NULL",
                                                                    @"path" : @"text NOT NULL"
                                                                    }];

    
}


/**
 *  储存城市数据
 */
+ (void)saveSubData:(NSArray *)data{
    
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    NSString *SQL = [NSString stringWithFormat:@"REPLACE INTO %@ (id, pid, title, path) VALUES (?, ?, ?, ?);", tableName];
    
    for (NSDictionary *dict in data) {
        [db executeUpdate:SQL, dict[@"id"], dict[@"pid"], dict[@"title"], dict[@"path"]];
    }
    
    
}


/**
 *  取出科目数据
 */
+ (NSArray <LYSubDataModel *>*)getSubjectData{

    //取出科目栏
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    FMResultSet *result = [db executeQuery:@"SELECT * FROM t_subject WHERE pid = 0"];
    
    NSMutableArray *models = [NSMutableArray array];
    
    while ([result next]) {
        
        NSMutableArray *subs = [NSMutableArray array];
        LYSubDataModel *model = [[LYSubDataModel alloc] init];
        
        NSString *title = [result stringForColumn:@"title"];
        int ID = [result intForColumn:@"id"];
        
        model.title = title;
       //取出科目栏对应科目
        FMResultSet *subResult =  [db executeQueryWithFormat:@"SELECT title FROM t_subject WHERE pid = %d", ID];
        while ([subResult next]) {
            
            NSString *subTitle = [subResult stringForColumnIndex:0];
            [subs addObject:subTitle];
            
        }
        
        model.subjects = subs;
        [models addObject:model];
    }
    
    return [models copy];

}

/**
 *  取科目id
 */
+ (NSInteger)getIDWithSubTitle:(NSString *)title{
    
    FMDatabase *db = [VVYSaveDataManager getDB];
    FMResultSet *result = [db executeQueryWithFormat:@"SELECT id FROM t_subject WHERE title = %@", title];
    while ([result next]) {
        NSInteger ID = [result intForColumnIndex:0];
        return ID;
    }
    return -1;

}

/**
 *  获取科目路径
 */
+ (NSString *)getSubpathWithSubTitle:(NSString *)title{

    FMDatabase *db = [VVYSaveDataManager getDB];
    FMResultSet *result = [db executeQueryWithFormat:@"SELECT path FROM t_subject WHERE title = %@",title];
    while ([result next]) {
        NSString *path = [result stringForColumnIndex:0];
        return path;
    }
    
    return nil;

}

@end
